using ADODB;
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using Microsoft.CommerceServer.Runtime;
using Microsoft.CommerceServer.Interop;
using Microsoft.CommerceServer.Interop.Catalog;
using Microsoft.CommerceServer.Runtime.Profiles;
using Microsoft.CommerceServer.Runtime.Configuration;
using System.Globalization;

namespace CS2K2CatalogSystem
{
	/// <summary>
	/// CatalogManager class is the main interface to access Commerce Server Catalog System.
	/// </summary>
	public class CatalogService
	{
		private ICatalogManager3 catMgr;
		private CacheManager cache = new CacheManager();
		private string catName = null;
		private string dbconStr = null;

		private const string CATALOG_CON_STRING_RESOURCE = "connstr_db_Catalog";
		private const string CATLOG_RESOURCE_KEY = "Product Catalog";

		public CatalogService()
		{
			CatalogInit();
		}

		public CatalogService(string catalogName)
		{
			catName = catalogName;
			CatalogInit();
		}

		private void CatalogInit()
		{
			// check to see if we have the connection string in the cache
			dbconStr = (string)cache.GetData(CATALOG_CON_STRING_RESOURCE);

			if(dbconStr==null)
			{
				dbconStr = (string)CommerceContext.Current.Resources[CATLOG_RESOURCE_KEY][CATALOG_CON_STRING_RESOURCE];
				cache.Add(CATALOG_CON_STRING_RESOURCE, ((object)dbconStr));
			}

			// initialize the catalog manager
			catMgr = new CatalogManagerClass();
			catMgr.Initialize(dbconStr, true, null);
		}

		public DataSet GetCatalogs(object lStartingRecord,object lNumRecords,ref object recInQuery)
		{
			//object recInQuery = null;
			_Recordset rs = catMgr.get_Catalogs(null, lStartingRecord, lNumRecords, null, ref recInQuery);

			return RecordSetToDataSet(rs, "Catalogs");
		}

		public DataSet GetCatalog(string catalogName)
		{
			_Recordset rs = catMgr.GetCatalog(catalogName).GetCatalogAttributes();
			
			return RecordSetToDataSet(rs, "CatalogAttributes");;
		}

		public DataSet GetRootCategories()
		{
			return GetRootCategories(this.catName);
		}

		public DataSet GetRootCategories(string catalogName)
		{
			_Recordset rs = catMgr.GetCatalog(catalogName).get_RootCategories(null, true);

			return RecordSetToDataSet(rs, "RootCategories");
		}

		private DataSet RecordSetToDataSet(_Recordset rs, string tableName)
		{
			if(tableName==null)
			{
				tableName="Table1";
			}
			OleDbDataAdapter ad = new OleDbDataAdapter();
			DataSet ds = new DataSet();
			ad.Fill(ds, rs, tableName);
			return ds;
		}

		public DataSet ChildCategories(string catalogName, string categoryName)
		{
			// we have to do this in two steps becuase if we have a catalog in a virtual catalog then we
			// don't show any child categories
			Category cat = catMgr.GetCatalog(catalogName).GetCategory(categoryName);
			if(cat!=null)
			{
				_Recordset rs =	cat.ChildCategories;
				return  RecordSetToDataSet(rs, "ChildCategories");
			}
			else
			{
				return null;
			}
		}

		public DataSet ChildCategories(string categoryName)
		{
			return ChildCategories(this.catName, categoryName);
		}

		public DataSet GetPropertiesToDisplayInList(string catalogName)
		{
			DataSet ds = new DataSet();
			DataTable tbl = ds.Tables.Add();
			DataColumn col = tbl.Columns.Add("DisplayInProductsList", typeof(string));
			DataRow row = null;

			// get the product catalog object to work with
			ProductCatalog prdCat = catMgr.GetCatalog(catalogName);
			
			// get the identifying product and variant properties
			string sProductID = prdCat.IdentifyingProductProperty;
			string sVariantID = prdCat.IdentifyingVariantProperty;

			// get the catalog properties
			_Recordset rs = catMgr.get_Properties(catalogName);
			
			// add fields into the dataset
			while(!rs.EOF)
			{
				if((bool)rs.Fields["DisplayInProductsList"].Value)
				{
					row = ds.Tables[0].NewRow();
					row["DisplayInProductsList"] = rs.Fields["PropertyName"].Value;
					tbl.Rows.Add(row);
				}
				rs.MoveNext();
			}

			// now add the identifying Product, variant and defintion
			row = ds.Tables[0].NewRow();
			row["DisplayInProductsList"] = "DefinitionName";
			tbl.Rows.Add(row);

			row = ds.Tables[0].NewRow();
			row["DisplayInProductsList"] = sProductID;
			tbl.Rows.Add(row);

			row = ds.Tables[0].NewRow();
			row["DisplayInProductsList"] = sVariantID;
			tbl.Rows.Add(row);

			return ds;
		}

		public DataSet RootProducts(string catalogName, string categoryName, object lStartingRecord, object lRecordsToRetrieve, ref object plTotalRecordsInQuery)
		{
			// for right now only get products and variants do this in two steps becuase if the user
			// added a whole catalog as part of a virtual catalog then we don't show any root products
			Category cat = catMgr.GetCatalog(catalogName).GetCategory(categoryName);
			if(cat!=null)
			{
				_Recordset rs =	cat.get_DescendantProducts(12, lStartingRecord, lRecordsToRetrieve, ref plTotalRecordsInQuery);
				return RecordSetToDataSet(rs, "RootProducts");
			}
			else
			{
				return null;
			}
		}

		public DataSet CatalogRootProducts(string catalogName, object lStartingRecord, object lRecordsToRetrieve, ref object plTotalRecordsInQuery)
		{
			// if we have lots of products then we need to page it
			// create a query to get the root products this query needs to work for all
			//WHERE (ParentOID = - 1) AND (i_ClassType = 4)
			//_Recordset rs = catMgr.GetCatalog(catalogName).RootProducts;

			string displayProp = null;
			// get the catalog properties
			_Recordset rsProp = catMgr.get_Properties(catalogName);
			
			// add fields into the dataset
			while(!rsProp.EOF)
			{
				if((bool)rsProp.Fields["DisplayInProductsList"].Value)
				{
					displayProp+=rsProp.Fields["PropertyName"].Value+",";
				}
				rsProp.MoveNext();
			}
			//displayProp=displayProp.Remove(displayProp.Length-1,1);
			_Recordset rs = catMgr.Query("ParentOID = - 1", catalogName, 12, null, "ProductID", true, lStartingRecord, lRecordsToRetrieve, ref plTotalRecordsInQuery);
			return RecordSetToDataSet(rs, "CatalogRootProducts");
		}

		public DataSet GetCategoryProducts(string catalogName, string categoryName, object lStartingRecord, object lRecordsToRetrieve, ref object plTotalRecordsInQuery)
		{
			_Recordset rs = catMgr.GetCatalog(catalogName).GetCategory(categoryName).get_Products(12, "ProductID", lStartingRecord, lRecordsToRetrieve, ref plTotalRecordsInQuery);
			return RecordSetToDataSet(rs, "Products");
		}

		public DataSet GetCatalogLanguages(string catalogName)
		{
			_Recordset rs = catMgr.GetCatalog(catalogName).GetLanguages();
			return RecordSetToDataSet(rs, "GetLanguages");
		}

		public DataSet GetLanguages(string xmlPath)
		{
			//do we have in the cache
			DataSet ds = (DataSet)cache.GetData("Langauges");

			if(ds==null)
			{
				ds = new DataSet();
				ds.ReadXml(xmlPath);
				cache.Add("Langauges", (object)ds);
			}
			return ds;
		}

		public DataSet GetCurrencies(string xmlPath)
		{
			DataSet ds = (DataSet)cache.GetData("Currencies");

			if(ds==null)
			{
				ds = new DataSet();
				ds.ReadXml(xmlPath);
				cache.Add("Currencies", (object)ds);
			}
			return ds;
		}

		public DataSet GetDependentCatalogs(string catalogName)
		{
			object lTotalRecordsinQuery = null;
			_Recordset rs = catMgr.GetCatalog(catalogName).GetDependentCatalogs(null, null, ref lTotalRecordsinQuery);
			return RecordSetToDataSet(rs, "Catalog");
		}

		public void DeleteCatalog(string catalogName)
		{
			catMgr.DeleteCatalog(catalogName);
		}

		public void RegenerateFreeTextSearchIndex(string catalogName)
		{
			catMgr.GetCatalog(catalogName).RegenerateFreeTextSearchIndex(false);
		}

		public DataSet Search(string strSQLClause, string strFTSPhrase, string strCatalogsToSearch, string eClassTypeRequired, string strPropertiesToReturn, string strPropertyToSortOn, bool fSortAscending, string lStartingRecord, string lRecordsToRetrieve, string varLanguage, ref object plTotalRecordsInQuery)
		{
			strPropertiesToReturn+=",Catalogname, ProductID";
			_Recordset rs = catMgr.Search(strSQLClause, strFTSPhrase, strCatalogsToSearch, eClassTypeRequired, strPropertiesToReturn, strPropertyToSortOn, fSortAscending, lStartingRecord, lRecordsToRetrieve, varLanguage, ref plTotalRecordsInQuery);
			return RecordSetToDataSet(rs, "Search");
		}

		public string SiteName()
		{
			string sitename=(string)cache.GetData("SiteName");

			if(sitename==null)
			{
				sitename = CommerceContext.Current.SiteName;
				cache.Add("SiteName", sitename);
			}
			return sitename;
		}

		public DataSet GetProductDefintions()
		{
			_Recordset rs = catMgr.ProductDefinitions;
			return RecordSetToDataSet(rs, "ProductDefintions");
		}

		public DataSet GetCategoryDefintions()
		{
			_Recordset rs = catMgr.CategoryDefinitions;
			return RecordSetToDataSet(rs, "CategoryDefinitions");
		}

		public DataSet GetProdcut(string catalogName, string ProductID)
		{
			// return a huge dataset with all the product properties
			DataSet ds = new DataSet();
			// get a product object
			Product prod = catMgr.GetCatalog(catalogName).GetProduct(ProductID);
			// get the product data
			ds.Tables.Add(RecordSetToDataSet(prod.GetProductProperties, "ProductProperties").Tables[0].Copy());
			// get the definition
			_Recordset rs = catMgr.GetDefinitionProperties(ds.Tables[0].Rows[0]["DefinitionName"].ToString());
			// put the recordset into our dataset
			ds.Tables.Add(RecordSetToDataSet(rs, "Definition").Tables[0].Copy());
			// get the variant data if we have it
			DataRow[] dr = ds.Tables["Definition"].Select("PropertyType='1'");
			if(dr.Length>0)
			{
				_Recordset rsVar = prod.get_Variants(null);
				ds.Tables.Add(RecordSetToDataSet(rsVar, "Variants").Tables[0].Copy());
			}
			// get relationship data
			_Recordset rsRleatedProd = prod.RelatedProducts;
			if(!rsRleatedProd.EOF)
			{
				ds.Tables.Add(RecordSetToDataSet(rsRleatedProd, "RelatedProducts").Tables[0].Copy());
			}
			// get the related category data
			_Recordset rsRelatedCat = prod.RelatedCategories;
			if(!rsRelatedCat.EOF)
			{
				DataTable dt = RecordSetToDataSet(rsRelatedCat, "RelatedProducts").Tables[0].Copy();
				ds.Merge(dt);
			}
			// get assigned category data
			_Recordset rsParentCat = prod.ParentCategories;
			if(!rsParentCat.EOF)
			{
				ds.Tables.Add(RecordSetToDataSet(rsParentCat, "AssignedCategories").Tables[0].Copy());
			}
			// get the primary parent
			string primaryParent = prod.PrimaryParent;
			string pricingCategory = prod.PricingCategory;
			string useCategoryPricing = prod.UseCategoryPricing.ToString();
			// create a dataset for assigned category product properties
			DataTable dtProperperties = new DataTable("AssignedCategoriesProperties");
			dtProperperties.Columns.Add("PrimaryParent", typeof(string));
			dtProperperties.Columns.Add("PricingCategory", typeof(string));
			dtProperperties.Columns.Add("UseCategoryPricing", typeof(string));
			DataRow rowProp = dtProperperties.NewRow();
			rowProp["PrimaryParent"]=primaryParent;
			rowProp["PricingCategory"]=pricingCategory;
			rowProp["UseCategoryPricing"]=useCategoryPricing;
			dtProperperties.Rows.Add(rowProp);
			// now add our table to the dataset
			ds.Tables.Add(dtProperperties);

			return ds;
		}

		public void DeleteProdcut(string catalogName, string ProductID)
		{
			catMgr.GetCatalog(catalogName).DeleteProduct(ProductID);
		}

		public DataSet GetDefinitionProperties(string strDefinitionName)
		{
			_Recordset rs = catMgr.GetDefinitionProperties(strDefinitionName);
			return RecordSetToDataSet(rs, "DefinitionProperties");
		}

		public DataSet GetProperties(string catalogName)
		{
			_Recordset rs = catMgr.get_Properties(catalogName);
			return RecordSetToDataSet(rs, "DefinitionProperties");
		}

		public void GetIdentifyingProperties(ref string ProductID, ref string VaraintID, string catalogName)
		{
			// get the product catalog object to work with
			ProductCatalog prdCat = catMgr.GetCatalog(catalogName);
			
			// get the identifying product and variant properties
			ProductID = prdCat.IdentifyingProductProperty;
			VaraintID = prdCat.IdentifyingVariantProperty;
		}

		public void SaveCatalogProperties(DataSet dsProperties, string catalogName)
		{
			ProductCatalog prodCat = catMgr.GetCatalog(catalogName);
			// deal with languages
			foreach(DataRow drLang in dsProperties.Tables["GetLanguages"].Rows)
			{
				// check to see if we are adding or removing
				if(drLang["Removed"].ToString()=="1")
				{
					prodCat.RemoveLanguage(drLang["Language"].ToString());
				}
				else if(drLang["Removed"].ToString()=="2")
				{
					prodCat.AddLanguage(drLang["Language"].ToString());
				}
			}
			// get the properties
			_Recordset rsCatalog = prodCat.GetCatalogAttributes();

			rsCatalog.Fields["StartDate"].Value = Convert.ToDateTime(dsProperties.Tables["CatalogProperties"].Rows[0]["StartDate"].ToString()).Date.ToString();
			rsCatalog.Fields["EndDate"].Value = Convert.ToDateTime(dsProperties.Tables["CatalogProperties"].Rows[0]["EndDate"].ToString()).Date.ToString();
			rsCatalog.Fields["CatalogName"].Value = catalogName;
			rsCatalog.Fields["Currency"].Value = dsProperties.Tables["CatalogProperties"].Rows[0]["Currency"].ToString();
			rsCatalog.Fields["DefaultLanguage"].Value = dsProperties.Tables["DefaultLanguages"].Rows[0]["DefaultLanguage"].ToString();
			rsCatalog.Fields["ReportingLanguage"].Value = dsProperties.Tables["DefaultLanguages"].Rows[0]["DefaultLanguageRep"].ToString();

			// deal with display names
			foreach(DataColumn colLang in dsProperties.Tables["CatalogProperties"].Columns)
			{
				// now get the displayanme columns
				if(colLang.ColumnName.StartsWith("DisplayName_"))
				{
					rsCatalog.Fields[colLang.ColumnName].Value=dsProperties.Tables["CatalogProperties"].Rows[0][colLang.ColumnName].ToString();
				}				
			}
			// update catalog properties
			prodCat.SetCatalogAttributes(rsCatalog, true);
		}

		public void SaveCategoryProperties(DataSet dsproperties, string catalogName)
		{
			// get a category object
			Category cat = catMgr.GetCatalog(catalogName).GetCategory(dsproperties.Tables["CategoryProperties"].Rows[0]["CategoryName"].ToString());
			// iterate through and save the category data
			foreach(DataTable dt in dsproperties.Tables)
			{
				switch(dt.TableName)
				{
					case "CategoryProperties":
						_Recordset rs = cat.GetCategoryProperties;
						// now that we have the table match and fields update the rcordset
						foreach(DataRow drDefinition in dsproperties.Tables["DefinitionProperties"].Rows)
						{
							rs.Fields[drDefinition["PropertyName"]].Value = dt.Rows[0][drDefinition["PropertyName"].ToString()];
						}
						// get the displayname
						rs.Fields["DisplayName"].Value = dt.Rows[0]["DisplayName"].ToString();
						// get the parent category
						cat.PrimaryParent = dt.Rows[0]["PrimaryParentCategory"].ToString();
						cat.SetCategoryProperties(rs, true);
						break;
					case "ParentCategories":
						// for right now make it simple and remove all parent categories then add 
						_Recordset rsParentCategories = cat.ParentCategories;
						while(!rsParentCategories.EOF)
						{
							cat.RemoveParentCategory(rsParentCategories.Fields["Name"].Value.ToString());
							rsParentCategories.MoveNext();
						}
						// now add parent category
						foreach(DataRow dr in dt.Rows)
						{
							cat.AddParentCategory(dr["Name"].ToString());
						}
						break;
					case "ChildCategories":
						// for right now just remove the child categories then add 
						_Recordset rsChildCategories = cat.ChildCategories;
						while(!rsChildCategories.EOF)
						{
							// remove category
							cat.RemoveChildCategory(rsChildCategories.Fields["CategoryName"].Value.ToString());
							rsChildCategories.MoveNext();
						}
						// now add child categories
						foreach(DataRow dr in dt.Rows)
						{
							cat.AddChildCategory(dr["CategoryName"].ToString());
						}
						break;
					case "RelatedProducts":
						// need to imporve this but for right now just delete and recreate
						_Recordset rsRealtedCategories = cat.RelatedCategories;
						while(!rsRealtedCategories.EOF)
						{
							cat.RemoveRelationshipToCategory(rsRealtedCategories.Fields["CategoryName"].Value.ToString(), rsRealtedCategories.Fields["RelationshipName"].Value.ToString(), rsRealtedCategories.Fields["TargetCatalog"].Value.ToString());
							rsRealtedCategories.MoveNext();
						}
						_Recordset rsRelatedProducts = cat.RelatedProducts;
						while(!rsRelatedProducts.EOF)
						{
							cat.RemoveRelationshipToProduct(rsRelatedProducts.Fields["ProductID"].Value.ToString(), rsRelatedProducts.Fields["RelationshipName"].Value.ToString(), rsRelatedProducts.Fields["TargetCatalog"].Value.ToString());
							rsRelatedProducts.MoveNext();
						}
						// add relationship
						// now create new products relation ship
						foreach(DataRow drRelatedProducts in dt.Rows)
						{
							if(drRelatedProducts["ProductId"]!=System.DBNull.Value)
							{
								cat.AddRelationshipToProduct(drRelatedProducts["ProductID"], drRelatedProducts["RelationShipName"].ToString(), drRelatedProducts["RelationShipDescription"], drRelatedProducts["TargetCatalog"].ToString());
							}
							else
							{
								cat.AddRelationshipToCategory(drRelatedProducts["CategoryName"].ToString(), drRelatedProducts["RelationShipName"].ToString(), drRelatedProducts["RelationShipDescription"], drRelatedProducts["TargetCatalog"].ToString());
							}
						}
						break;
				}
			}
		}
		
		public void SaveProductProperties(DataSet dsProperties, string catalogName)
		{
			// get the product object to work it
			Product prod = catMgr.GetCatalog(catalogName).GetProduct(dsProperties.Tables["ProductProperties"].Rows[0]["ProductID"]);
			// ok now go through the dataset and update approperiate data
			foreach(DataTable dt in dsProperties.Tables)
			{
				switch(dt.TableName)
				{
					case "ProductProperties":
						// get the product
						_Recordset rs = prod.GetProductProperties;
						// now that we have the table match the fields and update the rcordset
						foreach(DataRow drDefinition in dsProperties.Tables["Definition"].Rows)
						{
							rs.Fields[drDefinition["PropertyName"]].Value = dt.Rows[0][drDefinition["PropertyName"].ToString()];
						}
						// TODO: Improve Product Update
						// now that we have updated our recordset update the product
						// later on lets improve on this so we can get an error back if 
						// the data we are updating is newer than when we got it
						prod.SetProductProperties(rs, true);
						break;
					case "Variants":
						// iterate through the each variant
						foreach(DataRow drVariant in dt.Rows)
						{
							_Recordset rsVariant = new RecordsetClass();
							if(drVariant["VariantID"]==System.DBNull.Value)
							{
								// create a new variant
								string VaraintID = catMgr.GetCatalog(catalogName).IdentifyingVariantProperty;
								rsVariant = prod.CreateVariant(drVariant[VaraintID]);
							}
							else
							{
								// get the variant to wrok with
								rsVariant = prod.GetVariantProperties(drVariant["VariantID"]);
							}
							//_Recordset rsVariant = prod.GetVariantProperties(drVariant["VariantID"]);
							foreach(DataRow drDefinition in dsProperties.Tables["Definition"].Rows)
							{
								if(drDefinition["PropertyType"].ToString()=="1")
								{
									rsVariant.Fields[drDefinition["PropertyName"]].Value = drVariant[drDefinition["PropertyName"].ToString()];
								}
							}
							// update the varaint price
							rsVariant.Fields["cy_list_price"].Value =drVariant["cy_list_price"];
							// TODO: Improve variant Update
							// now we have updated our recordset update the product
							// later on lets improve on this so we can get an error back if 
							// the data we are updating is newer than when we got it
							prod.SetVariantProperties(rsVariant, true);
						}
						break;
					case "AssignedCategories":
						// need to improve this by passing a dataset that has fileds to tell us
						// if we have new parent categories but for now just remove everything
						// then add it as new
						_Recordset rsParentCategories = prod.ParentCategories;
						while(!rsParentCategories.EOF)
						{
							Category cat = catMgr.GetCatalog(catalogName).GetCategory(rsParentCategories.Fields["Name"].Value.ToString());
							cat.RemoveProduct(prod.ProductID);
							rsParentCategories.MoveNext();
						}
						// add as new
						foreach(DataRow drparentCategories in dt.Rows)
						{
							Category cat = catMgr.GetCatalog(catalogName).GetCategory(drparentCategories["Name"].ToString());
							cat.AddProduct(prod.ProductID);
						}
						// now set pricing and parent category
						if(dsProperties.Tables["AssignedCategoriesProperties"].Rows[0]["PrimaryParent"].ToString()!="")
						{
							prod.PrimaryParent=dsProperties.Tables["AssignedCategoriesProperties"].Rows[0]["PrimaryParent"].ToString();
						}
						if(dsProperties.Tables["AssignedCategoriesProperties"].Rows[0]["UseCategoryPricing"].ToString()=="False")
						{
							prod.UseCategoryPricing = false;
						}
						else
						{
							prod.UseCategoryPricing = true;
						}
						break;
					case "RelatedProducts":
						_Recordset rsRelatedProducts = new RecordsetClass();
						// deal with related products
						// for right now just remove everything then add them
						// later on improve on this by only updating what's new or updated
						rsRelatedProducts = prod.RelatedProducts;
						while(!rsRelatedProducts.EOF)
						{
							prod.RemoveRelationshipToProduct(rsRelatedProducts.Fields["ProductID"].Value.ToString(), rsRelatedProducts.Fields["RelationshipName"].Value.ToString(), rsRelatedProducts.Fields["TargetCatalog"].Value.ToString());
							rsRelatedProducts.MoveNext();
						}
						// now remove all categories relations ship
						rsRelatedProducts = prod.RelatedCategories;
						while(!rsRelatedProducts.EOF)
						{
							prod.RemoveRelationshipToCategory(rsRelatedProducts.Fields["CategoryName"].Value.ToString(), rsRelatedProducts.Fields["RelationshipName"].Value.ToString(), rsRelatedProducts.Fields["TargetCatalog"].Value.ToString());
							rsRelatedProducts.MoveNext();
						}
						// now create new products relation ship
						foreach(DataRow drRelatedProducts in dt.Rows)
						{
							if(drRelatedProducts["ProductId"]!=System.DBNull.Value)
							{
								prod.AddRelationshipToProduct(drRelatedProducts["ProductID"], drRelatedProducts["RelationShipName"].ToString(), drRelatedProducts["RelationShipDescription"], drRelatedProducts["TargetCatalog"].ToString());
							}
							else
							{
								prod.AddRelationshipToCategory(drRelatedProducts["CategoryName"].ToString(), drRelatedProducts["RelationShipName"].ToString(), drRelatedProducts["RelationShipDescription"], drRelatedProducts["TargetCatalog"].ToString());
							}
						}
						break;
				}
			}
		}

		public DataSet GetCategory(string catalogName, string categoryName)
		{
			DataSet ds = new DataSet();

			// get a catalog manager object
			Category cat = catMgr.GetCatalog(catalogName).GetCategory(categoryName);

			//' Retrieve Category Properties
			_Recordset rsProperties = cat.GetCategoryProperties;
			
			//' Retrieve Definition properties
			_Recordset rsDefinitionProperties = catMgr.GetDefinitionProperties(rsProperties.Fields["DefinitionName"].Value.ToString());
			
			ds.Tables.Add(RecordSetToDataSet(rsProperties, "CategoryProperties").Tables[0].Copy());
			ds.Tables.Add(RecordSetToDataSet(rsDefinitionProperties, "DefinitionProperties").Tables[0].Copy());

			//' get parent catgegories
			_Recordset rsParentCategories = cat.ParentCategories;
			ds.Tables.Add(RecordSetToDataSet(rsParentCategories, "ParentCategories").Tables[0].Copy());

			//' get child categories
			 _Recordset rsChildCategories = cat.ChildCategories;
			ds.Tables.Add(RecordSetToDataSet(rsChildCategories, "ChildCategories").Tables[0].Copy());
	
			// get the related products
			_Recordset rsRelatedProducts = cat.RelatedProducts;
			ds.Tables.Add(RecordSetToDataSet(rsRelatedProducts, "RelatedProducts").Tables[0].Copy());

			// get the related categories
			_Recordset rsRelatedCategories = cat.RelatedCategories;
			DataTable dtRelatedCategories = RecordSetToDataSet(rsRelatedCategories, "RelatedProducts").Tables[0].Copy();
			ds.Merge(dtRelatedCategories);

			//' then get  ancestros
			_Recordset rsAncestors = cat.AncestorCategories;
			DataTable dtAncestors = RecordSetToDataSet(rsAncestors, "Ancestors").Tables[0].Copy();

			//'******************** get a list of all categories available for child category ******************** 
			//' do a search to get only what we want
			string sPhrase = "(i_ClassType = 1) AND (CategoryName != N'"+categoryName+"')";
			object dummy = null;
			_Recordset rsCurrentChildCategories = catMgr.Query(sPhrase, catalogName, null, "CategoryName", "CategoryName", true, null, null, ref dummy);

			DataTable dtChild = new DataTable("AvailableChilds");
			dtChild.Columns.Add("CategoryName", typeof(string));

			// create an array that contains ancestors and child category
			ArrayList searchList = new ArrayList();
			// go through all ancestors
			foreach(DataRow drAncestors in dtAncestors.Rows)
			{
				searchList.Add(drAncestors["Name"]);
			}
			// go through all child categories
			foreach(DataRow drChildCat in ds.Tables["ChildCategories"].Rows)
			{
				searchList.Add(drChildCat["CategoryName"]);
			}

			while(!rsCurrentChildCategories.EOF)
			{
				// create a list for avaialble childs
				DataRow rowChild = dtChild.NewRow();
				// now do we have a valid data
				if(bIsValid(rsCurrentChildCategories.Fields["CategoryName"].Value.ToString(), searchList))
				{
					rowChild["CategoryName"] = rsCurrentChildCategories.Fields["CategoryName"].Value.ToString();
					dtChild.Rows.Add(rowChild);
				}
				rsCurrentChildCategories.MoveNext();
			}

			ds.Tables.Add(dtChild);
			return ds;
		}

		// got this from bizdesk
		private bool bIsValid(string sCategoryName, ArrayList aNonChildren)
		{
			int iIndex = 0;
			int iUBound = 0;

			iUBound = aNonChildren.Count;
			if(iUBound == -1)
			{
				return true;
			}

			for(iIndex = 0; iUBound>iIndex; iIndex++)
			{
				if(aNonChildren[iIndex].ToString() == sCategoryName)
				{
					aNonChildren.RemoveAt(iIndex);
					return false;
				}
			}
			return true;
		}

		public void NewBaseCatalog(DataSet dsProperties, string catalogName)
		{
			//
		}

		public void NewVirtualCatalog(DataSet dsProperties, string catalogName)
		{
			//
		}

		public void ImportXMLCatalog(string vrXML, bool fUpdateOnly, bool fRunSynchronously, bool fGenerateFullTextIndexes)
		{
			//
		}

		public void ExportXMLCatalog(string vrXML, object strCatalogsToExport, bool fRunSynchronously, int eXMLFmt, string strLang)
		{
			//
		}
	}
}
